|
|
| Book details / order |
| EXCEL 2016 POWER PROGRAMMING WITH VBA |
This book is indispensable for excel intermediate users, power users, and would-be power users. fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of excel tips, tricks and techniques readers won't find anywhere else. thoroughly updated to cover the excel interface, file formats, enhanced interactivity with other office applications, and upgraded collaboration features. this power-user's guide is packed with procedures, tips, and ideas for expanding excel's capabilities with visual basic for applications. includes templates and worksheets from the book that are downloadable on the website.
introduction
part i: introduction to excel vba
chapter 1: essentials of spreadsheet application development
what is a spreadsheet application?
steps for application development
determining user needs
planning an application that meets user needs
determining the most appropriate user interface
customizing the ribbon
customizing shortcut menus
creating shortcut keys
creating custom dialog boxes
using activex controls on a worksheet
executing the development effort
concerning yourself with the end user
testing the application
making the application bulletproof
making the application aesthetically appealing and intuitive
creating a user help system
documenting the development effort
distributing the application to the user
updating the application when necessary
other development issues
the user's installed version of excel
language issues
system speed
video modes
chapter 2: introducing visual basic for applications
getting a head start with the macro recorder
creating your first macro
comparing absolute and relative macro recording
other macro recording concepts
working with the visual basic editor
understanding vbe components
working with the project window
working with a code window
customizing the vba environment
the editor format tab
the general tab
the docking tab
vba fundamentals
understanding objects
understanding collections
understanding properties
deep dive: working with range objects
finding the properties of the range object
the range property
the cells property
the offset property
essential concepts to remember
don't panic--you are not alone
read the rest of the book
let excel help write your macro
use the help system
use the object browser
pilfer code from the internet
leverage user forums
visit expert blogs
mine youtube for video training
learn from the microsoft office dev center
dissect the other excel files in your organization
ask your local excel genius
chapter 3: vba programming fundamentals
vba language elements: an overview
comments
variables, data types and constants
defining data types
declaring variables
scoping variables
working with constants
working with strings
working with dates
assignment statements
arrays
declaring arrays
declaring multidimensional arrays
declaring dynamic arrays
object variables
user-defined data types
built-in functions
manipulating objects and collections
with-end with constructs
for each-next constructs
controlling code execution
goto statements
if-then constructs
select case constructs
looping blocks of instructions
chapter 4: working with vba sub procedures
about procedures
declaring a sub procedure
scoping a procedure
executing sub procedures
executing a procedure with the run sub / user form command
executing a procedure from the macro dialog box
executing a procedure with a ctrl+shortcut key combination
executing a procedure from the ribbon
executing a procedure from a customized shortcut menu
executing a procedure from another procedure
executing a procedure by clicking an object
executing a procedure when an event occurs
executing a procedure from the immediate window
passing arguments to procedures
error-handling techniques
trapping errors
error-handling examples
a realistic example that uses sub procedures
the goal
project requirements
what you know
the approach
some preliminary recording
initial setup
code writing
writing the sort procedure
more testing
fixing the problems
utility availability
evaluating the project
chapter 5: creating function procedures
sub procedures versus function procedures
why create custom functions?
an introductory function example
using the function in a worksheet
using the function in a vba procedure
analyzing the custom function
function procedures
a function's scope
executing function procedures
function arguments
function examples
functions with no argument
a function with one argument
a function with two arguments
a function with an array argument
a function with optional arguments
a function that returns a vba array
a function that returns an error value
a function with an indefinite number of arguments
emulating excel's sum function
extended date functions
debugging functions
dealing with the insert function dialog box
using the macro options method
specifying a function category
adding a function description manually
using add-ins to store custom functions
using the windows api
windows api examples
determining the windows directory
detecting the shift key
learning more about api functions
chapter 6: understanding excel's events
what you should know about events
understanding event sequences
where to put event-handler procedures
disabling events
entering event-handler code
event-handler procedures that use arguments
getting acquainted with workbook-level events
the open event
the activate event
the sheet activate event
the new sheet event
the before save event
the deactivate event
the before print event
the before close event
examining worksheet events
the change event
monitoring a specific range for changes
the selection change event
the before double click event
the before right click event
monitoring with application events
enabling application-level events
determining when a workbook is opened
monitoring application-level events
accessing events not associated with an object
the on time event
the on key event
chapter 7: vba programming examples and techniques
learning by example
working with ranges
copying a range
moving a range
copying a variably sized range
selecting or otherwise identifying various types of ranges
resizing a range
prompting for a cell value
entering a value in the next empty cell
pausing a macro to get a user-selected range
counting selected cells
determining the type of selected range
looping through a selected range efficiently
deleting all empty rows
duplicating rows a variable number of times
determining whether a range is contained in another range
determining a cell's data type
reading and writing ranges
a better way to write to a range
transferring one-dimensional arrays
transferring a range to a variant array
selecting cells by value
copying a noncontiguous range
working with workbooks and sheets
saving all workbooks
saving and closing all workbooks
hiding all but the selection
creating a hyperlink table of contents
synchronizing worksheets
vba techniques
toggling a boolean property
displaying the date and time
displaying friendly time
getting a list of fonts
sorting an array
processing a series of files
some useful functions for use in your code
the file exists function
the file name only function
the path exists function
the range name exists function
the sheet exists function
the work book is open function
retrieving a value from a closed workbook
some useful worksheet functions
returning cell formatting information
a talking worksheet
displaying the date when a file was saved or printed
understanding object parents
counting cells between two values
determining the last nonempty cell in a column or row
does a string match a pattern?
extracting the nth element from a string
spelling out a number
a multifunctional function
the sheetoffset function
returning the maximum value across all worksheets
returning an array of non-duplicated random integers
randomizing a range
sorting a range
windows api calls
understanding api declarations
determining file associations
determining default printer information
determining video display information
reading from and writing to the registry
part ii: advanced vba techniques
chapter 8: working with pivot tables
an introductory pivot table example
creating a pivot table
examining the recorded code for the pivot table
cleaning up the recorded pivot table code
creating a more complex pivot table
the code that created the pivot table
how the more complex pivot table works
creating multiple pivot tables
creating a reverse pivot table
chapter 9: working with charts
getting the inside scoop on charts
chart locations
the macro recorder and charts
the chart object model
creating an embedded chart
creating a chart on a chart sheet
modifying charts
using vba to activate a chart
moving a chart
using vba to deactivate a chart
determining whether a chart is activated
deleting from the chart objects or charts collection
looping through all charts
sizing and aligning chart objects
creating lots of charts
exporting a chart
exporting all graphics
changing the data used in a chart
changing chart data based on the active cell
using vba to determine the ranges used in a chart
using vba to display arbitrary data labels on a chart
displaying a chart in a user form
understanding chart events
an example of using chart events
enabling events for an embedded chart
example: using chart events with an embedded chart
discovering vba charting tricks
printing embedded charts on a full page
creating unlinked charts
displaying text with the mouse over event
scrolling a chart
working with sparkline charts
chapter 10: interacting with other applications
understanding microsoft office automation
understanding the concept of binding
a simple automation example
automating access from excel
running an access query from excel
running an access macro from excel
automating word from excel
sending excel data to a word document
simulating mail merge with a word document
automating powerpoint from excel
sending excel data to a powerpoint presentation
sending all excel charts to a powerpoint presentation
convert a workbook into a powerpoint presentation
automating outlook from excel
mailing the active workbook as an attachment
mailing a specific range as an attachment
mailing a single sheet as an attachment
mailing all email addresses in your contact list
starting other applications from excel
using the vba shell function
using the windows shell execute api function
using app activate
running control panel dialog boxes
chapter 11: working with external data and files
working with external data connections
manually creating a connection
manually editing data connections
using vba to create dynamic connections
iterating through all connections in a workbook
using ado and vba to pull external data
the connection string
declaring a record set
referencing the ado object library
putting it all together in code
using ado with the active workbook
working with text files
opening a text file
reading a text file
writing a text file
getting a file number
determining or setting the file position
statements for reading and writing
text file manipulation examples
importing data in a text file
exporting a range to a text file
importing a text file to a range
logging excel usage
filtering a text file
performing common file operations
using vba file-related statements
using the file system object object
zipping and unzipping files
zipping files
unzipping a file
part iii: working with user forms
chapter 12: leveraging custom dialog boxes
before you create that user form
using an input box
the vba input box function
the application.inputbox method
the vba msg box function
the excel get open filename method
the excel get save as filename method
prompting for a directory
displaying excel's built-in dialog boxes
displaying a data form
making the data form accessible
displaying a data form by using vba
chapter 13: introducing user forms
how excel handles custom dialog boxes
inserting a new user form
adding controls to a user form
toolbox controls
checkbox
combo box
command button
frame
image
label
list box
multipage
option button
ref edit
scroll bar
spin button
tab strip
text box
toggle button
adjusting user form controls
adjusting a control's properties
using the properties window
common properties
accommodating keyboard users
displaying a user form
adjusting the display position
displaying a modeless user form
displaying a user form based on a variable
loading a user form
about event-handler procedures
closing a user form
creating a user form: an example
creating the user form
writing code to display the dialog box
testing the dialog box
adding event-handler procedures
the finished dialog box
understanding user form events
learning about events
user form events
spin button events
pairing a spin button with a textbox
referencing user form controls
customizing the toolbox
adding new pages to the toolbox
customizing or combining controls
adding new activex controls
creating user form templates
a user form checklist
chapter 14: user form examples
creating a user form "menu"
using command buttons in a user form
using a list box in a user form
selecting ranges from a user form
creating a splash screen
disabling a user form's close button
changing a user form's size
zooming and scrolling a sheet from a user form
list box techniques
adding items to a list box control
determining the selected item in a list box
determining multiple selections in a list box
multiple lists in a single list box
list box item transfer
moving items in a list box
working with multicolumn list box controls
using a list box to select worksheet rows
using a list box to activate a sheet
filtering a list box from a text box
using the multi page control in a user form
using an external control
animating a label
chapter 15: advanced user form techniques
a modeless dialog box
displaying a progress indicator
creating a stand-alone progress indicator
showing a progress indicator that's integrated into a user form
creating a non-graphical progress indicator
creating wizards
setting up the multi page control for the wizard
adding the buttons to the wizard's user form
programming the wizard's buttons
programming dependencies in a wizard
performing the task with the wizard
emulating the msg box function
msg box emulation: my msg box code
how the my msg box function works
using the my msg box function
a user form with movable controls
a user form with no title bar
simulating a toolbar with a user form
emulating a task pane with a user form
a resizable user form
handling multiple user form controls with one event handler
selecting a color in a user form
displaying a chart in a user form
saving a chart as a gif file
changing the image control's picture property
making a user form semitransparent
a puzzle on a user form
video poker on a user form
part iv: developing excel applications
chapter 16: creating and using add-ins
what is an add-in?
comparing an add-in with a standard workbook
why create add-ins?
understanding excel's add-in manager
creating an add-in
an add-in example
adding descriptive information for the example add-in
creating an add-in
installing an add-in
testing the add-in
distributing an add-in
modifying an add-in
comparing xlam and xlsm files
xlam file vba collection membership
visibility of xlsm and xlam files
worksheets and chart sheets in xlsm and xlam files
accessing vba procedures in an add-in
manipulating add-ins with vba
adding an item to the add ins collection
removing an item from the add ins collection
add in object properties
accessing an add-in as a workbook
add in object events
optimizing the performance of add-ins
special problems with add-ins
ensuring that an add-in is installed
referencing other files from an add-in
detecting the proper excel version for your add-in
chapter 17: working with the ribbon
ribbon basics
customizing the ribbon
adding a button to the ribbon
adding a button to the quick access toolbar
understanding the limitations of ribbon customization
creating a custom ribbon
adding a button to an existing tab
adding a check box to an existing tab
ribbon controls demo
a dynamic menu control example
more on ribbon customization
using vba with the ribbon
accessing a ribbon control
working with the ribbon
activating a tab
creating an old-style toolbar
limitations of old-style toolbars in excel 2007 and later
code to create a toolbar
chapter 18: working with shortcut menus
command bar overview
command bar types
listing shortcut menus
referring to command bars
referring to controls in a command bar
properties of command bar controls
displaying all shortcut menu items
using vba to customize shortcut menus
shortcut menu and the single-document interface
resetting a shortcut menu
disabling a shortcut menu
disabling shortcut menu items
adding a new item to the cell shortcut menu
adding a submenu to a shortcut menu
limiting a shortcut menu to a single workbook
shortcut menus and events
adding and deleting menus automatically
disabling or hiding shortcut menu items
creating a context-sensitive shortcut menu
chapter 19: providing help for your applications
help for your excel applications
help systems that use excel components
using cell comments for help
using a text box for help
using a worksheet to display help text
displaying help in a user form
displaying help in a web browser
using html files
using an mhtml file
using the html help system
using the help method to display html help
associating a help file with your application
associating a help topic with a vba function
chapter 20: leveraging class modules
what is a class module?
built-in class modules
custom class modules
creating a num lock class
inserting a class module
adding vba code to the class module
using the c num lock class
coding properties, methods and events
programming properties of objects
programming methods for objects
class module events
exposing a query table event
creating a class to hold classes
creating the c sales rep a
Author : Michael alexander, dick kusleika
Publication : Wiley
Isbn : 9788126560608
Store book number : 107
NRS 1600.00
|
 |
|
|
|
|
|
|
|
| |
|
|
